-- 商品数据插入脚本
-- 生成1000条商品记录

-- 清空现有商品数据（谨慎执行）
-- DELETE FROM wms_product;
-- ALTER TABLE wms_product AUTO_INCREMENT = 1;

-- 开始事务
BEGIN;

-- 插入水稻类产品（约125条）
INSERT INTO wms_product (product_name, product_code, category_id, unit, spec, weight, price, shelf_life, storage_condition, status, create_by, create_time, remark)
SELECT 
    CONCAT('双城', 
        CASE FLOOR(RAND() * 5) 
            WHEN 0 THEN '珍珠'
            WHEN 1 THEN '香禾'
            WHEN 2 THEN '稻花香'
            WHEN 3 THEN '寒地'
            ELSE '北国'
        END, 
        '水稻', LPAD((@row_number:=@row_number+1), 3, '0')),
    CONCAT('SC-RICE-', LPAD(@row_number, 4, '0')),
    9, -- 水稻分类ID
    '公斤',
    CONCAT(CASE FLOOR(RAND() * 3) 
        WHEN 0 THEN '优质'
        WHEN 1 THEN '特级'
        ELSE '精选'
    END, CASE FLOOR(RAND() * 3)
        WHEN 0 THEN '长粒'
        WHEN 1 THEN '圆粒'
        ELSE '珍珠'
    END, '大米'),
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 5
        WHEN 1 THEN 10
        ELSE 25
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 5.99 + RAND() * 2
        WHEN 1 THEN 7.99 + RAND() * 3
        ELSE 9.99 + RAND() * 4
    END,
    365,
    '阴凉干燥处',
    '0',
    'admin',
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY),
    '双城区特产水稻'
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
    (SELECT @row_number:=0) t4
LIMIT 125;

-- 插入小麦类产品（约125条）
INSERT INTO wms_product (product_name, product_code, category_id, unit, spec, weight, price, shelf_life, storage_condition, status, create_by, create_time, remark)
SELECT 
    CONCAT('双城', 
        CASE FLOOR(RAND() * 4) 
            WHEN 0 THEN '硬质'
            WHEN 1 THEN '红麦'
            WHEN 2 THEN '优质'
            ELSE '高筋'
        END, 
        '小麦', LPAD((@row_number:=@row_number+1), 3, '0')),
    CONCAT('SC-WHEAT-', LPAD(@row_number, 4, '0')),
    10, -- 小麦分类ID
    '公斤',
    CONCAT(CASE FLOOR(RAND() * 3) 
        WHEN 0 THEN '优质'
        WHEN 1 THEN '特级'
        ELSE '精选'
    END, CASE FLOOR(RAND() * 2)
        WHEN 0 THEN '硬质'
        ELSE '软质'
    END, '小麦'),
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 5
        WHEN 1 THEN 10
        ELSE 25
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 4.99 + RAND() * 2
        WHEN 1 THEN 6.99 + RAND() * 2
        ELSE 8.99 + RAND() * 3
    END,
    365,
    '阴凉干燥处',
    '0',
    'admin',
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY),
    '双城区特产小麦'
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
    (SELECT @row_number:=125) t4
LIMIT 125;

-- 插入玉米类产品（约125条）
INSERT INTO wms_product (product_name, product_code, category_id, unit, spec, weight, price, shelf_life, storage_condition, status, create_by, create_time, remark)
SELECT 
    CONCAT('双城', 
        CASE FLOOR(RAND() * 4) 
            WHEN 0 THEN '甜'
            WHEN 1 THEN '糯'
            WHEN 2 THEN '鲜'
            ELSE '脱水'
        END, 
        '玉米', LPAD((@row_number:=@row_number+1), 3, '0')),
    CONCAT('SC-CORN-', LPAD(@row_number, 4, '0')),
    11, -- 玉米分类ID
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN '公斤'
        WHEN 1 THEN '袋'
        ELSE '箱'
    END,
    CONCAT(CASE FLOOR(RAND() * 3) 
        WHEN 0 THEN '优质'
        WHEN 1 THEN '特级'
        ELSE '精选'
    END, CASE FLOOR(RAND() * 2)
        WHEN 0 THEN '甜'
        ELSE '糯'
    END, '玉米'),
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 5
        WHEN 1 THEN 10
        ELSE 20
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 3.99 + RAND() * 2
        WHEN 1 THEN 5.99 + RAND() * 2
        ELSE 7.99 + RAND() * 3
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 180
        WHEN 1 THEN 270
        ELSE 365
    END,
    CASE FLOOR(RAND() * 2)
        WHEN 0 THEN '阴凉干燥处'
        ELSE '冷藏保存'
    END,
    '0',
    'admin',
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY),
    '双城区特产玉米'
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
    (SELECT @row_number:=250) t4
LIMIT 125;

-- 插入大豆类产品（约125条）
INSERT INTO wms_product (product_name, product_code, category_id, unit, spec, weight, price, shelf_life, storage_condition, status, create_by, create_time, remark)
SELECT 
    CONCAT('双城', 
        CASE FLOOR(RAND() * 4) 
            WHEN 0 THEN '黄'
            WHEN 1 THEN '黑'
            WHEN 2 THEN '绿'
            ELSE '有机'
        END, 
        '大豆', LPAD((@row_number:=@row_number+1), 3, '0')),
    CONCAT('SC-SOY-', LPAD(@row_number, 4, '0')),
    12, -- 大豆分类ID
    '公斤',
    CONCAT(CASE FLOOR(RAND() * 3) 
        WHEN 0 THEN '优质'
        WHEN 1 THEN '特级'
        ELSE '精选'
    END, CASE FLOOR(RAND() * 3)
        WHEN 0 THEN '黄'
        WHEN 1 THEN '黑'
        ELSE '绿'
    END, '大豆'),
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 5
        WHEN 1 THEN 10
        ELSE 25
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 6.99 + RAND() * 2
        WHEN 1 THEN 8.99 + RAND() * 3
        ELSE 10.99 + RAND() * 4
    END,
    365,
    '阴凉干燥处',
    '0',
    'admin',
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY),
    '双城区特产大豆'
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
    (SELECT @row_number:=375) t4
LIMIT 125;

-- 插入蔬菜类产品（约125条）
INSERT INTO wms_product (product_name, product_code, category_id, unit, spec, weight, price, shelf_life, storage_condition, status, create_by, create_time, remark)
SELECT 
    CONCAT('双城', 
        CASE MOD(@row_number, 15) 
            WHEN 0 THEN '白菜'
            WHEN 1 THEN '土豆'
            WHEN 2 THEN '胡萝卜'
            WHEN 3 THEN '洋葱'
            WHEN 4 THEN '大蒜'
            WHEN 5 THEN '黄瓜'
            WHEN 6 THEN '西红柿'
            WHEN 7 THEN '茄子'
            WHEN 8 THEN '辣椒'
            WHEN 9 THEN '南瓜'
            WHEN 10 THEN '豆角'
            WHEN 11 THEN '豌豆'
            WHEN 12 THEN '香菇'
            WHEN 13 THEN '平菇'
            ELSE '木耳'
        END, 
        LPAD((@row_number:=@row_number+1), 3, '0')),
    CONCAT('SC-VEG-', LPAD(@row_number, 4, '0')),
    CASE MOD(@row_number, 15) 
        WHEN 0 THEN 18 -- 叶菜类
        WHEN 1 THEN 19 -- 根茎类
        WHEN 2 THEN 19 -- 根茎类
        WHEN 3 THEN 19 -- 根茎类
        WHEN 4 THEN 19 -- 根茎类
        WHEN 5 THEN 20 -- 瓜果类
        WHEN 6 THEN 20 -- 瓜果类
        WHEN 7 THEN 20 -- 瓜果类
        WHEN 8 THEN 20 -- 瓜果类
        WHEN 9 THEN 20 -- 瓜果类
        WHEN 10 THEN 21 -- 豆类
        WHEN 11 THEN 21 -- 豆类
        WHEN 12 THEN 22 -- 菌类
        WHEN 13 THEN 22 -- 菌类
        ELSE 22 -- 菌类
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN '公斤'
        WHEN 1 THEN '袋'
        ELSE '箱'
    END,
    CONCAT(CASE FLOOR(RAND() * 3) 
        WHEN 0 THEN '优质'
        WHEN 1 THEN '特级'
        ELSE '精选'
    END, '新鲜蔬菜'),
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 1
        WHEN 1 THEN 5
        ELSE 10
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 2.99 + RAND() * 2
        WHEN 1 THEN 4.99 + RAND() * 3
        ELSE 7.99 + RAND() * 4
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 7
        WHEN 1 THEN 14
        ELSE 30
    END,
    CASE FLOOR(RAND() * 2)
        WHEN 0 THEN '冷藏保存'
        ELSE '阴凉通风处'
    END,
    '0',
    'admin',
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY),
    '双城区新鲜蔬菜'
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
    (SELECT @row_number:=500) t4
LIMIT 125;

-- 插入水果类产品（约125条）
INSERT INTO wms_product (product_name, product_code, category_id, unit, spec, weight, price, shelf_life, storage_condition, status, create_by, create_time, remark)
SELECT 
    CONCAT('双城', 
        CASE MOD(@row_number, 9) 
            WHEN 0 THEN '草莓'
            WHEN 1 THEN '蓝莓'
            WHEN 2 THEN '树莓'
            WHEN 3 THEN '李子'
            WHEN 4 THEN '樱桃'
            WHEN 5 THEN '杏'
            WHEN 6 THEN '苹果'
            WHEN 7 THEN '梨'
            ELSE '山楂'
        END, 
        LPAD((@row_number:=@row_number+1), 3, '0')),
    CONCAT('SC-FRUIT-', LPAD(@row_number, 4, '0')),
    CASE MOD(@row_number, 9) 
        WHEN 0 THEN 23 -- 浆果类
        WHEN 1 THEN 23 -- 浆果类
        WHEN 2 THEN 23 -- 浆果类
        WHEN 3 THEN 24 -- 核果类
        WHEN 4 THEN 24 -- 核果类
        WHEN 5 THEN 24 -- 核果类
        WHEN 6 THEN 25 -- 仁果类
        WHEN 7 THEN 25 -- 仁果类
        ELSE 25 -- 仁果类
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN '公斤'
        WHEN 1 THEN '盒'
        ELSE '箱'
    END,
    CONCAT(CASE FLOOR(RAND() * 3) 
        WHEN 0 THEN '优质'
        WHEN 1 THEN '特级'
        ELSE '精选'
    END, '新鲜水果'),
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 0.5
        WHEN 1 THEN 1
        ELSE 5
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 9.99 + RAND() * 5
        WHEN 1 THEN 14.99 + RAND() * 10
        ELSE 24.99 + RAND() * 15
    END,
    CASE FLOOR(RAND() * 3)
        WHEN 0 THEN 7
        WHEN 1 THEN 14
        ELSE 30
    END,
    CASE FLOOR(RAND() * 2)
        WHEN 0 THEN '冷藏保存'
        ELSE '阴凉通风处'
    END,
    '0',
    'admin',
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY),
    '双城区新鲜水果'
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
    (SELECT @row_number:=625) t4
LIMIT 125;

-- 插入畜牧产品（约125条）
INSERT INTO wms_product (product_name, product_code, category_id, unit, spec, weight, price, shelf_life, storage_condition, status, create_by, create_time, remark)
SELECT 
    CONCAT('双城', 
        CASE MOD(@row_number, 10) 
            WHEN 0 THEN '猪肉'
            WHEN 1 THEN '牛肉'
            WHEN 2 THEN '羊肉'
            WHEN 3 THEN '鸡肉'
            WHEN 4 THEN '鸭肉'
            WHEN 5 THEN '鹅肉'
            WHEN 6 THEN '鸡蛋'
            WHEN 7 THEN '鸭蛋'
            WHEN 8 THEN '牛奶'
            ELSE '蜂蜜'
        END, 
        LPAD((@row_number:=@row_number+1), 3, '0')),
    CONCAT('SC-LIVE-', LPAD(@row_number, 4, '0')),
    CASE MOD(@row_number, 10) 
        WHEN 0 THEN 26 -- 肉类
        WHEN 1 THEN 26 -- 肉类
        WHEN 2 THEN 26 -- 肉类
        WHEN 3 THEN 26 -- 肉类
        WHEN 4 THEN 26 -- 肉类
        WHEN 5 THEN 26 -- 肉类
        WHEN 6 THEN 27 -- 蛋类
        WHEN 7 THEN 27 -- 蛋类
        WHEN 8 THEN 28 -- 奶类
        ELSE 29 -- 蜂蜜
    END,
    CASE MOD(@row_number, 10)
        WHEN 8 THEN '升'
        WHEN 9 THEN '瓶'
        WHEN 6, 7 THEN '盒'
        ELSE '公斤'
    END,
    CONCAT(CASE FLOOR(RAND() * 3) 
        WHEN 0 THEN '优质'
        WHEN 1 THEN '特级'
        ELSE '精选'
    END, '畜牧产品'),
    CASE MOD(@row_number, 10)
        WHEN 6, 7 THEN 0.5
        WHEN 8 THEN 1
        WHEN 9 THEN 0.5
        ELSE CASE FLOOR(RAND() * 3)
            WHEN 0 THEN 0.5
            WHEN 1 THEN 1
            ELSE 2
        END
    END,
    CASE MOD(@row_number, 10)
        WHEN 0 THEN 29.99 + RAND() * 10
        WHEN 1 THEN 49.99 + RAND() * 20
        WHEN 2 THEN 39.99 + RAND() * 15
        WHEN 3 THEN 19.99 + RAND() * 10
        WHEN 4 THEN 24.99 + RAND() * 10
        WHEN 5 THEN 29.99 + RAND() * 10
        WHEN 6 THEN 9.99 + RAND() * 5
        WHEN 7 THEN 12.99 + RAND() * 5
        WHEN 8 THEN 6.99 + RAND() * 3
        ELSE 39.99 + RAND() * 20
    END,
    CASE MOD(@row_number, 10)
        WHEN 9 THEN 365
        WHEN 8 THEN 14
        WHEN 6, 7 THEN 30
        ELSE 7
    END,
    CASE MOD(@row_number, 10)
        WHEN 9 THEN '阴凉干燥处'
        ELSE '冷藏保存'
    END,
    '0',
    'admin',
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY),
    '双城区畜牧产品'
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
    (SELECT @row_number:=750) t4
LIMIT 125;

-- 插入加工食品（约125条）
INSERT INTO wms_product (product_name, product_code, category_id, unit, spec, weight, price, shelf_life, storage_condition, status, create_by, create_time, remark)
SELECT 
    CONCAT('双城', 
        CASE MOD(@row_number, 12) 
            WHEN 0 THEN '大米粉'
            WHEN 1 THEN '小麦粉'
            WHEN 2 THEN '玉米粉'
            WHEN 3 THEN '香肠'
            WHEN 4 THEN '腊肉'
            WHEN 5 THEN '火腿'
            WHEN 6 THEN '酸奶'
            WHEN 7 THEN '奶酪'
            WHEN 8 THEN '豆腐'
            WHEN 9 THEN '豆干'
            WHEN 10 THEN '果脯'
            ELSE '白酒'
        END, 
        LPAD((@row_number:=@row_number+1), 3, '0')),
    CONCAT('SC-PROC-', LPAD(@row_number, 4, '0')),
    CASE MOD(@row_number, 12) 
        WHEN 0 THEN 36 -- 米面制品
        WHEN 1 THEN 36 -- 米面制品
        WHEN 2 THEN 36 -- 米面制品
        WHEN 3 THEN 37 -- 肉制品
        WHEN 4 THEN 37 -- 肉制品
        WHEN 5 THEN 37 -- 肉制品
        WHEN 6 THEN 38 -- 乳制品
        WHEN 7 THEN 38 -- 乳制品
        WHEN 8 THEN 39 -- 豆制品
        WHEN 9 THEN 39 -- 豆制品
        WHEN 10 THEN 40 -- 蜜饯
        ELSE 41 -- 酒类
    END,
    CASE MOD(@row_number, 12)
        WHEN 6 THEN '盒'
        WHEN 7 THEN '块'
        WHEN 8, 9 THEN '块'
        WHEN 10 THEN '包'
        WHEN 11 THEN '瓶'
        ELSE '公斤'
    END,
    CONCAT(CASE FLOOR(RAND() * 3) 
        WHEN 0 THEN '优质'
        WHEN 1 THEN '特级'
        ELSE '精选'
    END, '加工食品'),
    CASE MOD(@row_number, 12)
        WHEN 6 THEN 0.5
        WHEN 7 THEN 0.2
        WHEN 8, 9 THEN 0.5
        WHEN 10 THEN 0.25
        WHEN 11 THEN 0.5
        ELSE CASE FLOOR(RAND() * 3)
            WHEN 0 THEN 0.5
            WHEN 1 THEN 1
            ELSE 2
        END
    END,
    CASE MOD(@row_number, 12)
        WHEN 0, 1, 2 THEN 8.99 + RAND() * 5
        WHEN 3, 4, 5 THEN 29.99 + RAND() * 20
        WHEN 6, 7 THEN 12.99 + RAND() * 7
        WHEN 8, 9 THEN 5.99 + RAND() * 3
        WHEN 10 THEN 15.99 + RAND() * 10
        ELSE 99.99 + RAND() * 100
    END,
    CASE MOD(@row_number, 12)
        WHEN 11 THEN 1825
        WHEN 10 THEN 180
        WHEN 0, 1, 2 THEN 365
        WHEN 3, 4, 5 THEN 90
        WHEN 6, 7 THEN 30
        ELSE 14
    END,
    CASE MOD(@row_number, 12)
        WHEN 6, 7, 8, 9 THEN '冷藏保存'
        ELSE '阴凉干燥处'
    END,
    '0',
    'admin',
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY),
    '双城区加工食品'
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
    (SELECT @row_number:=875) t4
LIMIT 125;

-- 提交事务
COMMIT;

-- 验证插入结果
-- SELECT COUNT(*) FROM wms_product;
-- SELECT * FROM wms_product LIMIT 10; 